using System;
using System.Data;
using System.Collections.Generic;
using System.IO;
using Oracle.DataAccess.Client;
using Wicresoft.SelfService.DAO;
using System.Linq;
using System.Configuration;
using System.Web;
using System.Text;

namespace Wicresoft.SelfService.DAL
{
    public class ITServiceDAL : IDisposable
    {
        private OracleDataAccess oda;
        private OracleTransaction oTran;
        public ITServiceDAL()
        {
            oda = new OracleDataAccess();
            oda.Open();
        }
        #region  DBM for ServiceList
        public ServiceList getServiceList_By_serviceTypeID(int serviceTypeID)
        {
            ServiceList serviceList = new ServiceList();
            QueryParameters[] param = new QueryParameters[] {
                new QueryParameters(){ OracleDbName="ID", OracleDbType=OracleDbType.Int32, OracleDbValue=serviceTypeID, QueryOperator=QueryOperatorType.equal, PredicateType=PredicateType.where}
            };
            serviceList = this.getServiceList_By_QueryParams(param, null, null, null).FirstOrDefault();
            return serviceList;
        }
        public List<ServiceList> getServiceList_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceList> modelList = new List<ServiceList>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.SERVICELIST", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    ServiceList model = new ServiceList();
                    var id = dr["id"];
                    model.serviceTypeID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    model.serviceName = Convert.ToString(dr["serviceName"]);
                    var serviceID = dr["serviceID"];
                    model.serviceID = serviceID == DBNull.Value ? default(int) : Convert.ToInt32(serviceID);
                    var needApprove = dr["needApprove"];
                    model.needApprove = needApprove == DBNull.Value ? default(byte) : Convert.ToByte(needApprove);
                    var cp = dr["cp"];
                    model.cp = cp == DBNull.Value ? default(byte) : Convert.ToByte(cp);
                    var isEvent = dr["isEvent"];
                    model.isEvent = isEvent == DBNull.Value ? default(byte) : Convert.ToByte(isEvent);
                    model.applvl1 = Convert.ToString(dr["applvl1"]);
                    model.applvl2 = Convert.ToString(dr["applvl2"]);
                    model.applvl3 = Convert.ToString(dr["applvl3"]);
                    model.partialName = Convert.ToString(dr["partialName"]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        public List<ServiceList> getAllServiceList()
        {
            List<ServiceList> list = new List<ServiceList>();
            string sqlStr = "select * from servicelist where serviceID >0 order by ID";
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr))
            {
                while (dr.Read())
                {
                    ServiceList model = new ServiceList();
                    var id = dr["id"];
                    model.serviceTypeID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    model.serviceName = Convert.ToString(dr["serviceName"]);
                    var serviceID = dr["serviceID"];
                    model.serviceID = serviceID == DBNull.Value ? default(int) : Convert.ToInt32(serviceID);
                    var needApprove = dr["needApprove"];
                    model.needApprove = needApprove == DBNull.Value ? default(byte) : Convert.ToByte(needApprove);
                    var cp = dr["cp"];
                    model.cp = cp == DBNull.Value ? default(byte) : Convert.ToByte(cp);
                    var isEvent = dr["isEvent"];
                    model.isEvent = isEvent == DBNull.Value ? default(byte) : Convert.ToByte(isEvent);
                    model.applvl1 = Convert.ToString(dr["applvl1"]);
                    model.applvl2 = Convert.ToString(dr["applvl2"]);
                    model.applvl3 = Convert.ToString(dr["applvl3"]);
                    model.partialName = Convert.ToString(dr["partialName"]);
                    list.Add(model);
                }
            }
            return list;
        }
        #endregion
        #region  DBM for ApproveSteps
        public ApproveSteps getApproveSteps_By_ID(int? id)
        {
            ApproveSteps approveSteps = new ApproveSteps();
            if (id.HasValue == false) return null;
            QueryParameters[] param = new QueryParameters[] {
                new QueryParameters(){ OracleDbName="id", OracleDbType=OracleDbType.Int32, OracleDbValue=id.Value, QueryOperator=QueryOperatorType.equal, PredicateType=PredicateType.where}
            };
            approveSteps = this.getApproveSteps_By_QueryParams(param, null, null, null).FirstOrDefault();
            return approveSteps;
        }
        public List<ApproveSteps> getApproveSteps_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ApproveSteps> modelList = new List<ApproveSteps>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.STEPS", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    ApproveSteps model = new ApproveSteps();
                    var id = dr["id"];
                    model.id = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    model.stepName = Convert.ToString(dr["stepName"]);
                    model.operation = Convert.ToString(dr["operation"]);
                    model.approver = Convert.ToString(dr["approver"]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        #endregion
        #region  DBM for ApproveRole
        public ApproveRole getApproveRole_By_roleName(string roleName)
        {
            ApproveRole approveRole = new ApproveRole();
            if (string.IsNullOrEmpty(roleName)) return null;
            QueryParameters[] param = new QueryParameters[] {
                new QueryParameters(){ OracleDbName="ROLENAME", OracleDbType=OracleDbType.NVarchar2, OracleDbValue=roleName, QueryOperator=QueryOperatorType.equal, PredicateType=PredicateType.where}
            };
            approveRole = this.getApproveRole_By_QueryParams(param, null, null, null).FirstOrDefault();
            return approveRole;
        }
        public List<ApproveRole> getApproveRole_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ApproveRole> modelList = new List<ApproveRole>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.APPROVALROLE", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    ApproveRole model = new ApproveRole();
                    var id = dr["id"];
                    model.id = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    model.roleName = Convert.ToString(dr["roleName"]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        #endregion
        #region  DBM for UserRoleRelationship
        public UserRoleRelationship getUserRoleRelationship_By_ID(int? id)
        {
            UserRoleRelationship ship = new UserRoleRelationship();
            if (id.HasValue == false) return null;
            QueryParameters[] param = new QueryParameters[] {
                new QueryParameters(){ OracleDbName="ROLEID", OracleDbType=OracleDbType.Int32, OracleDbValue=id.Value, QueryOperator=QueryOperatorType.equal, PredicateType=PredicateType.where}
            };
            ship = this.getUserRoleRelationship_By_QueryParams(param, null, null, null).FirstOrDefault();
            return ship;
        }
        public List<UserRoleRelationship> getUserRoleRelationship_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<UserRoleRelationship> modelList = new List<UserRoleRelationship>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.USERROLERELATIONSHIP", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    UserRoleRelationship model = new UserRoleRelationship();
                    var roleID = dr["roleID"];
                    model.roleID = roleID == DBNull.Value ? default(int) : Convert.ToInt32(roleID);
                    model.accountID = Convert.ToString(dr["accountID"]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        public bool updateUserRoleRelationship_By_QueryParams(QueryParameters[] param, out int rowInfluenced)
        {
            rowInfluenced = default(int);
            //try
            //{
            DBMType type = DBMType.update;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.USERROLERELATIONSHIP", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            rowInfluenced = oda.ExecuteCommand(sqlStr, commandParameters);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        public bool insertUserRoleRelationship(QueryParameters[] param, out int currVal)
        {
            currVal = default(int);
            //try
            //{
            DBMType type = DBMType.insert;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.APPROVALROLE", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            object obj = oda.ExecuteScalar(sqlStr, commandParameters);
            currVal = Convert.ToInt32(obj);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        public bool deleteUserRoleRelationship_By_QueryParams(QueryParameters[] param, out int rowInfluenced)
        {
            rowInfluenced = default(int);
            //try
            //{
            DBMType type = DBMType.delete;
            string sqlStr = this.PrepareSqlStr(type, "ADMIN.APPROVALROLE", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            rowInfluenced = oda.ExecuteCommand(sqlStr, commandParameters);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        #endregion
        #region DBM for attachment
        public List<Attachment> getAttachment_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<Attachment> atts = new List<Attachment>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "Attachment", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);

            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    Attachment att = new Attachment();
                    var id = dr["ID"];
                    att.ID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    att.fileName = Convert.ToString(dr["fileName"]);
                    att.fileSize = Convert.ToString(dr["fileSize"]);
                    var uploadDate = dr["DATETIME"];
                    att.uploadDate = uploadDate == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(uploadDate);
                    att.committerID = Convert.ToString(dr["accountID"]);
                    att.committerUserName = Convert.ToString(dr["USERNAME"]);
                    atts.Add(att);
                }
            }
            return atts;
        }
        public bool createAttacment_By_QueryParams(QueryParameters[] param, string seqName, out int currVal)
        {
            currVal = default(int);
            //try
            //{
            DBMType type = DBMType.insert;
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            //string sqlStr = string.Concat("begin\r", this.PrepareSqlStr(type, "Attachment", param, null, null, null), ";\r select ", seqName, " from dual;\r end;");
            string sqlStr = this.PrepareSqlStr(type, "Attachment", param, null, null, null);
            object obj = oda.ExecuteScalar(sqlStr, null, commandParameters);
            currVal = Convert.ToInt32(obj);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        public bool deleteAttacment_By_QueryParams(QueryParameters[] param, out int rowInfluenced)
        {
            rowInfluenced = default(int);
            //try
            //{
            DBMType type = DBMType.delete;
            string sqlStr = this.PrepareSqlStr(type, "Attachment", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            rowInfluenced = oda.ExecuteCommand(sqlStr, commandParameters);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        #endregion
        #region DBM for AccountCopy

        public List<string> getAdministrativeByCompany(string company)
        {
            List<string> list = new List<string>();
            if (!string.IsNullOrEmpty(company))
            {
                string Sql = "select * from fj_administrativeregion where company=:company";
                OracleParameter[] commandParameters = new OracleParameter[]{
                new OracleParameter("company",company)};

                OracleDataAccess oda2 = new OracleDataAccess(ConfigurationManager.AppSettings["ITSM"]);
                using (OracleDataReader dr = oda2.ExecuteReader(Sql, commandParameters))
                {
                    while (dr.Read())
                    {
                        list.Add(Convert.ToString(dr["administrativeregion"]));
                    }
                }

                if (oda2 != null)
                    oda2.Close();

            }
            return list;
        }

        public bool updateUerInfoToPortal(AccountInfo ai)
        {
            if (ai == null) return false;
            if (string.IsNullOrEmpty(ai.Account)) return false;
            string Sql = "update user_x set department=:dept,position=:position where name=:accountid";
            OracleParameter[] commandParameters = new OracleParameter[]{
                new OracleParameter("dept",ai.Dept),
                new OracleParameter("position",ai.Position),
                new OracleParameter("accountid",ai.Account)
            };

            bool ret = true;
            OracleDataAccess oda2 = null;
            try
            {
                oda2 = new OracleDataAccess(ConfigurationManager.AppSettings["ITPortal"]);
                if (oda2.ExecuteCommand(Sql, commandParameters) > 0)
                    ret = true;
                else
                    ret = false;
            }
            catch
            {
                ret = false;
            }
            finally
            {
                if (oda2 != null)
                    oda2.Close();
            }
            return ret;
        }

        public bool updateUerInfo(AccountInfo ai)
        {
            if (ai == null) return false;
            if (string.IsNullOrEmpty(ai.Account)) return false;
            string Sql = "update AccountCopy set dept=:dept,position=:position,phone=:phone,email=:email where accountid=:accountid";
            OracleParameter[] commandParameters = new OracleParameter[]{
                new OracleParameter("dept",ai.Dept),
                new OracleParameter("position",ai.Position),
                new OracleParameter("phone",ai.Phone),
                new OracleParameter("email",ai.Mail),
                new OracleParameter("accountid",ai.Account)
            };

            try
            {
                oda.ExecuteCommand(Sql, commandParameters);
                return true;
            }
            catch
            {
                return false;
            }

        }

        public UserInfo getUserInfo_By_AccountID(string accountID)
        {
            UserInfo userInfo = new UserInfo();
            QueryParameters[] param = new QueryParameters[] {
                new QueryParameters(){ OracleDbName="accountID", OracleDbType=OracleDbType.NVarchar2, OracleDbValue=accountID, QueryOperator=QueryOperatorType.equal, PredicateType=PredicateType.where}
            };
            userInfo = this.getUserInfo_By_QueryParams(param, null, null, null).FirstOrDefault();
            return userInfo;
        }
        public List<UserInfo> getUserInfo_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<UserInfo> uis = new List<UserInfo>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "AccountCopy", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);

            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    UserInfo ui = new UserInfo();
                    ui.accountID = Convert.ToString(dr["accountID"]);
                    ui.userName = Convert.ToString(dr["userName"]);
                    ui.company = Convert.ToString(dr["company"]);
                    ui.dept = Convert.ToString(dr["dept"]);
                    ui.position = Convert.ToString(dr["position"]);
                    ui.phone = Convert.ToString(dr["phone"]);
                    ui.email = Convert.ToString(dr["email"]);
                    uis.Add(ui);
                }
            }
            return uis;
        }
        #endregion
        #region DBM for BreakDown
        public List<BreakDownList> getBreakDownList_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<BreakDownList> bdList = new List<BreakDownList>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "BreakDown", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    BreakDownList bd = new BreakDownList();
                    var id = dr["ID"];
                    bd.ID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    bd.LvlName = Convert.ToString(dr["LvlName"]);
                    var pid = dr["parentID"];
                    bd.parentID = pid == DBNull.Value ? default(int) : Convert.ToInt32(pid);
                    bdList.Add(bd);
                }
            }
            return bdList;
        }
        #endregion
        #region DBM for ServiceRecords
        public string getCurrentProcessor_By_RequestID(string serviceRequestID)
        {
            string currentProcessor = string.Empty;
            string sqlStr = string.Concat("select accountcopy.username,approvalrole.rolename from servicerecords,steps,approvalrole,userrolerelationship,accountcopy ",
                " where servicerecords.step = steps.id and steps.approver = approvalrole.rolename and approvalrole.id = userrolerelationship.roleid ",
                " and userrolerelationship.accountid = accountcopy.accountid and (accountcopy.company = servicerecords.companyids or servicerecords.step >2) ",
                " and servicerecords.SERVICEREQUESTID = :serviceRequestID ");
            OracleParameter[] commandParameters = new OracleParameter[]{
                new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, serviceRequestID, ParameterDirection.Input)
            };
            DataTable dt = oda.ExecuteFillToDataTable(sqlStr, commandParameters);
            if (dt.Rows.Count > 1)
            {
                currentProcessor = this.mapRoleNameCN(dt.Rows[0]["rolename"].ToString());
            }
            else if (dt.Rows.Count == 1)
            {
                currentProcessor = dt.Rows[0]["username"].ToString();
            }
            return currentProcessor;
        }

        public List<ServiceDetail> getServiceDetail_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceDetail> sds = new List<ServiceDetail>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ServiceRecords", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    ServiceDetail sd = new ServiceDetail();
                    var id = dr["ID"];
                    sd.ID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    sd.serviceRequestID = Convert.ToString(dr["serviceRequestID"]);
                    sd.flowInstanceId = Convert.ToString(dr["flowInstanceId"]);
                    sd.accountID = Convert.ToString(dr["accountID"]);
                    var serviceTypeID = dr["ServiceTypeID"];
                    sd.serviceTypeID = serviceTypeID == DBNull.Value ? default(int) : Convert.ToInt32(serviceTypeID);
                    //var breakDownID = dr["BreakDownID"];
                    //sd.breakDownID = breakDownID == DBNull.Value ? default(int) : Convert.ToInt32(breakDownID);
                    sd.appName = Convert.ToString(dr["AppSystem"]);
                    sd.computerName = Convert.ToString(dr["ComputerName"]);
                    sd.ipAdress = Convert.ToString(dr["ipAdress"]);
                    sd.os = Convert.ToString(dr["os"]);
                    sd.mac = Convert.ToString(dr["mac"]);
                    sd.browser = Convert.ToString(dr["browser"]);
                    sd.description = Convert.ToString(dr["description"]);
                    var stauts = dr["stauts"];
                    sd.stauts = stauts == DBNull.Value ? default(Int16) : Convert.ToInt32(stauts);
                    var stepid = dr["step"];
                    sd.stepID = stepid == DBNull.Value ? default(Int16) : Convert.ToInt32(stepid);
                    sd.createTime = Convert.ToDateTime(dr["createTime"]);
                    var updatetime = dr["updateTime"];
                    if (updatetime == DBNull.Value)
                    {
                        sd.updateTime = null;
                    }
                    else
                    {
                        sd.updateTime = Convert.ToDateTime(updatetime);
                    }
                    sd.approveGroup = Convert.ToString(dr["approveGroup"]);
                    sd.approveOwner = Convert.ToString(dr["approveOwner"]);
                    sd.companyIDs = Convert.ToString(dr["companyIDs"]);
                    sd.BDDescription = Convert.ToString(dr["BDDescription"]);
                    sd.BDSummary = Convert.ToString(dr["BDSummary"]);
                    sd.breakDownlvl1 = Convert.ToString(dr["breakDownlvl1"]);
                    sd.breakDownlvl2 = Convert.ToString(dr["breakDownlvl2"]);
                    sd.breakDownlvl3 = Convert.ToString(dr["breakDownlvl3"]);
                    sd.IncidentNumber = Convert.ToString(dr["IncidentNumber"]);
                    sd.AdministrativeRegion = Convert.ToString(dr["administrativeregion"]);
                    sd.SubmitType = 1;//自服务提交
                    sds.Add(sd);
                }
            }
            return sds;
        }
        public bool createServiceDetail(QueryParameters[] param, string seqName, out int currVal)
        {
            currVal = default(int);
            //try
            //{
            DBMType type = DBMType.insert;
            string sqlStr = this.PrepareSqlStr(type, "ServiceRecords", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            if (oTran == null)
            {
                oda.Open();
                oTran = oda.conn.BeginTransaction();
            }
            object obj = oda.ExecuteScalar(sqlStr, oTran, commandParameters);
            currVal = Convert.ToInt32(obj);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        public bool updateServiceDetail_By_QueryParams(QueryParameters[] param, out int rowInfluenced)
        {
            rowInfluenced = default(int);
            //try
            //{
            DBMType type = DBMType.update;
            string sqlStr = this.PrepareSqlStr(type, "ServiceRecords", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            if (oTran == null)
            {
                oda.Open();
                oTran = oda.conn.BeginTransaction();
            }
            rowInfluenced = oda.ExecuteCommand(sqlStr, oTran, commandParameters);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        #endregion
        #region DBM for BusinessSystem
        public List<BusinessSystem> getBusinessSystem_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<BusinessSystem> modelList = new List<BusinessSystem>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "BusinessSystem", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    BusinessSystem model = new BusinessSystem();
                    var id = dr["ID"];
                    model.ID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                    model.Name = Convert.ToString(dr["Name"]);
                    model.Company = Convert.ToString(dr["Company"]);
                    model.category = Convert.ToString(dr["CATEGORY"]);
                    var isUnified = dr["ISUNIFIED"];
                    model.isUnified = isUnified == DBNull.Value ? 0 : Convert.ToInt16(isUnified);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        #endregion

        #region DBM for ServiceRecordsHistory
        public List<ServiceHistory> getServiceHistory_By_QueryParams(QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceHistory> shs = new List<ServiceHistory>();
            DBMType type = DBMType.select;
            string sqlStr = this.PrepareSqlStr(type, "ServiceRecordsHistory", param, orderBy, ps, pi);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);

            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    ServiceHistory sh = new ServiceHistory();
                    sh.serviceRequestID = Convert.ToString(dr["serviceRequestID"]);
                    sh.approverID = Convert.ToString(dr["accountID"]);
                    sh.approverName = Convert.ToString(dr["USERNAME"]);
                    sh.action = Convert.ToString(dr["action"]);
                    sh.comments = Convert.ToString(dr["comments"]);
                    var approveDate = dr["Datetime"];
                    sh.approveDate = approveDate == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(approveDate);
                    shs.Add(sh);
                }
            }
            return shs;
        }
        public bool createServiceHistory(QueryParameters[] param, out int rowInfluenced)
        {
            rowInfluenced = default(int);
            //try
            //{
            DBMType type = DBMType.insert;
            string sqlStr = this.PrepareSqlStr(type, "ServiceRecordsHistory", param, null, null, null);
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);
            if (oTran == null)
            {
                oda.Open();
                oTran = oda.conn.BeginTransaction();
            }
            rowInfluenced = oda.ExecuteCommand(sqlStr, oTran, commandParameters);
            return true;
            //}
            //catch
            //{
            //    return false;
            //}
        }
        #endregion
        #region DBM for special
        public List<UserInfo> getUserInfo_By_ITServiceRecord(QueryParameters[] param)
        {
            List<UserInfo> uis = new List<UserInfo>();
            string sqlStr = @"select ac.*,ar.ROLENAME from ServiceRecords sr inner join Steps s on sr.STEP=s.ID 
inner join APPROVALROLE ar on s.APPROVER=ar.ROLENAME 
inner join USERROLERELATIONSHIP ur on ur.ROLEID=ar.ID 
inner join ACCOUNTCOPY ac on ac.ACCOUNTID=ur.ACCOUNTID ";

            string whereSqlStr = this.PrepareSqlWhereOrSetStr(param, PredicateType.where, " and ");
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);

            using (OracleDataReader dr = oda.ExecuteReader(string.Concat(sqlStr, whereSqlStr), commandParameters))
            {
                while (dr.Read())
                {
                    UserInfo ui = new UserInfo();
                    ui.accountID = Convert.ToString(dr["accountID"]);
                    ui.userName = Convert.ToString(dr["userName"]);
                    ui.company = Convert.ToString(dr["company"]);
                    ui.dept = Convert.ToString(dr["dept"]);
                    ui.position = Convert.ToString(dr["position"]);
                    ui.phone = Convert.ToString(dr["phone"]);
                    ui.email = Convert.ToString(dr["email"]);
                    ui.roleName = Convert.ToString(dr["ROLENAME"]);
                    uis.Add(ui);
                }
            }
            return uis;
        }
        public List<UserInfo> getUserInfo_By_Stepid(QueryParameters[] param)
        {
            List<UserInfo> uis = new List<UserInfo>();
            string sqlStr = @"select ac.*,ar.ROLENAME from Steps s
inner join APPROVALROLE ar on s.APPROVER=ar.ROLENAME 
inner join USERROLERELATIONSHIP ur on ur.ROLEID=ar.ID 
inner join ACCOUNTCOPY ac on ac.ACCOUNTID=ur.ACCOUNTID ";

            string whereSqlStr = this.PrepareSqlWhereOrSetStr(param, PredicateType.where, " and ");
            OracleParameter[] commandParameters = this.PrepareOracleParameter(param);

            using (OracleDataReader dr = oda.ExecuteReader(string.Concat(sqlStr, whereSqlStr), commandParameters))
            {
                while (dr.Read())
                {
                    UserInfo ui = new UserInfo();
                    ui.accountID = Convert.ToString(dr["accountID"]);
                    ui.userName = Convert.ToString(dr["userName"]);
                    ui.company = Convert.ToString(dr["company"]);
                    ui.dept = Convert.ToString(dr["dept"]);
                    ui.position = Convert.ToString(dr["position"]);
                    ui.phone = Convert.ToString(dr["phone"]);
                    ui.email = Convert.ToString(dr["email"]);
                    ui.roleName = Convert.ToString(dr["ROLENAME"]);
                    uis.Add(ui);
                }
            }
            return uis;
        }

        public List<string> getAllOS()
        {
            List<string> osList = new List<string>();
            string sql = "select OSName from OS_TABLE";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    osList.Add(dr["OSName"].ToString());
                }
            }
            return osList;

        }

        public string createRecordNumberID()
        {
            string transaction = ("SET TRANSACTION ISOLATION LEVEL READ COMMITTED ");
            string selectIDStr = "SELECT ID FROM RECORDNUMBER ";
            string selectCurrentNumberStr = "SELECT CURRENTNUMBER FROM RECORDNUMBER ";
            string updateIDStr = "UPDATE RECORDNUMBER SET ID=ID+1 ";
            string commit = "COMMIT";
            string sysTime;
            oda.ExecuteCommand(transaction);
            using (OracleDataReader dr = oda.ExecuteReader(selectCurrentNumberStr))
            {
                dr.Read();
                sysTime = System.DateTime.Now.Year.ToString() + System.DateTime.Now.Month.ToString().PadLeft(2, '0') + System.DateTime.Now.Day.ToString().PadLeft(2, '0');
                if (!sysTime.Equals(dr["CURRENTNUMBER"].ToString().Substring(0, 8)))
                {
                    oda.ExecuteCommand("UPDATE RECORDNUMBER SET ID=1,CURRENTNUMBER=" + sysTime + "001 ");
                    oda.ExecuteCommand(commit);
                    return (sysTime + "001");
                }
                else
                {
                    using (OracleDataReader IDdr = oda.ExecuteReader(selectIDStr))
                    {
                        IDdr.Read();
                        string num = (Int32.Parse(IDdr["ID"].ToString()) + 1).ToString().PadLeft(3, '0');
                        string date = sysTime;
                        string code = date + num;
                        string sql = "UPDATE RECORDNUMBER SET CURRENTNUMBER=" + code + " ";
                        oda.ExecuteCommand(updateIDStr);
                        oda.ExecuteCommand(sql);
                        oda.ExecuteCommand(commit);
                        return code;

                    }
                }

            }
        }
        #endregion
        #region DBM for RolesConfig
        public bool isAdmin(string AccountID)
        {
            bool flag = false;
            string sql = "select * from USERROLERELATIONSHIP where ACCOUNTID='" + AccountID + "'" + " and isadmin=1";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                if (dr.HasRows == true) flag = true;
            }
            return flag;
        }
        public List<RolesConfig> getRolesInfoBy(QueryParameters[] param, int ps, int pi, out int counts)
        {
            List<RolesConfig> RCList = new List<RolesConfig>();
            string sqlStr = "select * from (select ac.accountID,ac.username,ac.company,ar.ID ROLEID ,ur.isadmin,row_number() over (order by ac.accountID ) AS \"row_number\" from accountcopy ac left join userrolerelationship ur on ur.accountid = ac.accountid left join approvalrole ar on ur.roleid=ar.id ";
            string countsSqlStr = "select count(*) count from (select ac.accountID,ac.username,ac.company,ar.ID ROLEID ,ur.isadmin from accountcopy ac left join userrolerelationship ur on ur.accountid = ac.accountid left join approvalrole ar on ur.roleid=ar.id ";
            string whereStr = " where 1=1 ";
            string whereStrCount = " where 1=1 ";
            OracleParameter[] commandParameters = new OracleParameter[param.Length];
            OracleParameter[] countCommandParameters = new OracleParameter[param.Length];
            int c = 0;
            for (int i = 0; i < param.Length; i++)
            {
                if (param[i].OracleDbValue != string.Empty)
                {
                    string myOracleDbName = param[i].OracleDbName.ToLower();
                    switch (myOracleDbName)
                    {
                        case "accountid":
                            {
                                whereStr = whereStr + " and ac.accountid like :accountid";
                                whereStrCount += " and ac.accountid like:accountid";
                                commandParameters[i] = new OracleParameter("accountid", OracleDbType.NVarchar2, "%" + param[i].OracleDbValue + "%", ParameterDirection.Input);
                                countCommandParameters[i] = new OracleParameter("accountid", OracleDbType.NVarchar2, "%" + param[i].OracleDbValue + "%", ParameterDirection.Input);
                                break;
                            }
                        case "username":
                            {
                                whereStr = whereStr + " and ac.username like:username";
                                whereStrCount += " and ac.username like:username";
                                commandParameters[i] = new OracleParameter("username", OracleDbType.NVarchar2, "%" + param[i].OracleDbValue + "%", ParameterDirection.Input);
                                countCommandParameters[i] = new OracleParameter("username", OracleDbType.NVarchar2, "%" + param[i].OracleDbValue + "%", ParameterDirection.Input);
                                break;
                            }
                        case "roleid":
                            {
                                if ((param[i].OracleDbValue != null && int.Parse(param[i].OracleDbValue.ToString()) != 0))
                                {
                                    if (int.Parse(param[i].OracleDbValue.ToString()) == 6)
                                    {
                                        whereStr = whereStr + " and (roleid=:roleid or roleid is null)";
                                        whereStrCount += " and (roleid=:roleid or roleid is null)";
                                        commandParameters[i] = new OracleParameter("roleid", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                        countCommandParameters[i] = new OracleParameter("roleid", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                    }
                                    else
                                    {
                                        whereStr = whereStr + " and roleid=:roleid";
                                        whereStrCount += " and roleid=:roleid";
                                        commandParameters[i] = new OracleParameter("roleid", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                        countCommandParameters[i] = new OracleParameter("roleid", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                    }
                                }
                                break;
                            }
                        case "isadmin":
                            {

                                if (param[i].OracleDbValue.ToString() == "0" || param[i].OracleDbValue.ToString() == "1")
                                {
                                    if (param[i].OracleDbValue.ToString() == "0")
                                    {
                                        whereStr += " and ( isadmin=:isadmin or isadmin is null ) ";
                                        whereStrCount += " and ( isadmin=:isadmin or isadmin is null ) ";
                                    }
                                    else
                                    {
                                        whereStr += " and isadmin=:isadmin";
                                        whereStrCount += " and isadmin=:isadmin";
                                    }
                                    commandParameters[i] = new OracleParameter("isadmin", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                    countCommandParameters[i] = new OracleParameter("isadmin", OracleDbType.Int16, param[i].OracleDbValue, ParameterDirection.Input);
                                }
                                break;
                            }
                        case "company":
                            {
                                string value = Enum.GetName(typeof(Wicresoft.SelfService.DAO.CompanyCollection), param[i].OracleDbValue);
                                whereStr += " and company=:company ";
                                whereStrCount += " and company=:company ";
                                commandParameters[i] = new OracleParameter("company", OracleDbType.NVarchar2, value, ParameterDirection.Input);
                                countCommandParameters[i] = new OracleParameter("company", OracleDbType.NVarchar2, value, ParameterDirection.Input);
                                break;
                            }
                        default: break;
                    }
                }
            }
            int beginIndex = (pi - 1) * ps;
            sqlStr += whereStr;
            sqlStr += " order by ac.accountID)";
            sqlStr += "  where \"row_number\">" + beginIndex + " and rownum<=" + ps;
            countsSqlStr += whereStrCount;
            countsSqlStr += " order by ac.accountID)";
            using (OracleDataReader countDR = oda.ExecuteReader(countsSqlStr, countCommandParameters))
            {
                countDR.Read();
                c = int.Parse(countDR["count"].ToString());
                counts = c;
            }
            using (OracleDataReader dr = oda.ExecuteReader(sqlStr, commandParameters))
            {
                while (dr.Read())
                {
                    RolesConfig RC = new RolesConfig();
                    RC.accountID = dr["accountID"].ToString();
                    RC.userName = dr["username"].ToString();
                    RC.company = dr["company"].ToString();
                    var role = dr["roleID"];
                    int roleid = dr["roleID"] == DBNull.Value ? 6 : int.Parse(dr["roleID"].ToString());
                    RC.roleName = Enum.GetName(typeof(Wicresoft.SelfService.DAO.RoleCollection), roleid);
                    var myisAdmin = dr["isAdmin"];
                    int intIsAdmin = myisAdmin == DBNull.Value ? 0 : Convert.ToInt32(myisAdmin);
                    RC.isAdmin = intIsAdmin == 1 ? "是" : "否";
                    RCList.Add(RC);
                }
            }
            return RCList;
        }


        #region  //BreakDown

        public List<BreakConfig> getAllFirstLvlBreakInfo()
        {
            List<BreakConfig> breakInfos = new List<BreakConfig>();
            //string sql = "select * from breakdown where parentid is null and lvlname!='其他' order by lvlname";
            string sql = "select * from breakdown where parentid is null order by lvlname";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    BreakConfig bk = createBreak(dr);
                    breakInfos.Add(bk);
                }
            }
            return breakInfos;
        }

        public List<BreakConfig> getAllSecondLvlBreakInfo()
        {
            List<BreakConfig> breakInfos = new List<BreakConfig>();
            string sql = "select * from breakdown b where b.parentid in (select id  from breakdown where parentid is null)";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    BreakConfig bk = createBreak(dr);
                    breakInfos.Add(bk);
                }
            }
            return breakInfos;
        }

        public BreakConfig getSecondLvlBreakInfo(int ID)
        {
            BreakConfig bk = new BreakConfig();
            string sql = "select * from breakdown where id="+ID;
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                dr.Read();
                bk = createBreak(dr);
            }
            return bk;
        
        }

        public BreakConfig getFirstLvlBreakInfoByName(string name)
        {
            BreakConfig bk = new BreakConfig();
            string sql = "select * from breakdown where lvlname='" + name + "' and parentid is null";
            using (var reader = oda.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    if (reader["ID"] != DBNull.Value) bk.ID = Convert.ToInt32(reader["ID"]);
                    if (reader["LvlName"] != DBNull.Value) bk.LvlName = (string)reader["LvlName"];
                }
            }
            return bk;
        }

        public BreakConfig getSecondLvlBreakInfoByName(string name)
        {
            BreakConfig bk = new BreakConfig();
            string sql = "select * from breakdown where lvlname='" + name + "' and parentid is not null";
            using (var reader = oda.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    if (reader["ID"] != DBNull.Value) bk.ID = Convert.ToInt32(reader["ID"]);
                    if (reader["LvlName"] != DBNull.Value) bk.LvlName = (string)reader["LvlName"];
                    break;
                }
            }
            return bk;
        }

        public BreakConfig getFirstLvlBreakInfo(int secondId)
        {
            BreakConfig bk = new BreakConfig();
            string sql = string.Format("select * from breakdown where id=(select parentid from breakdown where id={0})",secondId);
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                dr.Read();
                bk = createBreak(dr);
            }
            return bk;

        }

        public bool updateBreak(int id, string name,int parentid)
        {
            string sql = string.Format("update breakdown set lvlname='{0}', parentid={1} where id={2}", name, parentid, id);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        
        }

        public bool deleteBreak(int id) 
        {
            string sql = string.Format("delete from breakdown where id={0}", id);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private BreakConfig createBreak(OracleDataReader dr)
        {

            BreakConfig bk = new BreakConfig();
            bk.ID = Convert.ToInt32(dr["ID"]);
            bk.LvlName = dr["LVLNAME"].ToString();
            bk.Parentid = (dr["PARENTID"] is DBNull) ? 0 : Convert.ToInt32(dr["PARENTID"]);
            return bk;
        }

        public int createBreakdwon(BreakConfig model, int order)
        {
            BreakConfig m;
            if (order == 1)
            {
                m = getFirstLvlBreakInfoByName(model.LvlName);
                if (m.LvlName == null)
                {
                    string sql = string.Format("insert into breakdown(lvlname)values('{0}')", model.LvlName);
                    int count = oda.ExecuteCommand(sql);
                    if (count > 0)
                    {
                        var m1 = getFirstLvlBreakInfoByName(model.LvlName);
                        if (m1.ID != null)
                            return m1.ID;
                        else
                            return 0;
                    }
                    else
                    {
                        return 0;
                    }
                }
                else
                    return m.ID;
            }
            else
            {
                string sql = string.Format("insert into breakdown(lvlname,parentid)values('{0}',{1})", model.LvlName, model.Parentid);
                int count = oda.ExecuteCommand(sql);
                if (count > 0)
                {
                    var m1 = getSecondLvlBreakInfoByName(model.LvlName);
                    if (m1.LvlName != null)
                        return m1.ID;
                    else
                        return 0;
                }
                else
                {
                    return 0;
                }
            }

        }

        public bool searchByApplvl1(string applvl1)
        {
            var mlist = getAllFirstLvlBreakInfo();
            bool reslut = true;
            string n = null;
            if (mlist.Count > 0)
            {
                foreach (var item in mlist)
                {
                    n = item.LvlName.ToLower();
                    if (n == applvl1.ToLower())
                    {
                        reslut = false;
                        break;
                    }
                }
            }
            return reslut;
        }
        
        public bool searchByAppLvl2(int parentid, string applvl2)
        {
            var mlist = getAllSecondLvlBreakInfo();
            bool reslut = true;
            string n = null;
            if (mlist.Count > 0) 
            {
                foreach (var item in mlist) 
                {
                    n = item.LvlName.ToLower();
                    //if (n == applvl2.ToLower()) 
                    if (n == applvl2.ToLower() && item.Parentid == parentid)
                    {
                        reslut = false;
                        break;
                    }
                }
            }
            return reslut;
        }

        #endregion

        #region  //ServiceList

        public List<ClassifyConfig> getClassify()
        {
            List<ClassifyConfig> classifys = new List<ClassifyConfig>();
            string sql = "select *  from servicelist where partialname != 'BreakDown'";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    ClassifyConfig classify = new ClassifyConfig();

                    classify = createClassify(dr);
                    classifys.Add(classify);
                }

            }

            return classifys;

        }

        private List<ClassifyConfig> getClassifyLvl2() 
        {
            List<ClassifyConfig> classifys = new List<ClassifyConfig>();
            string sql = "select *  from servicelist where partialname != 'BreakDown' and serviceid is not null order by servicename";
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                while (dr.Read())
                {
                    ClassifyConfig classify = new ClassifyConfig();

                    classify = createClassify(dr);
                    classifys.Add(classify);
                }

            }

            return classifys;
        }

        public ClassifyConfig getClassifyByID(int ID)
        {
            ClassifyConfig classify = new ClassifyConfig();
            string sql = "select * from servicelist where ID=" + ID;
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                dr.Read();
                classify = createClassify(dr);
            }
            return classify;
        }

        public bool updateClassify(ClassifyConfig classify)
        {
            string sql = string.Format("update servicelist set servicename='{0}',applvl1='{1}',applvl2='{2}',applvl3='{3}' where ID={4}",
                classify.ServiceName, classify.Applvl1, classify.Applvl2, classify.Applvl3, classify.ID);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool createClassify(ClassifyConfig model) 
        {
            string sql = string.Format("insert into ServiceList(servicename,serviceid,needapprove,cp,isevent,applvl1,applvl2,applvl3,partialname)values('{0}',{1},{2},{3},{4},'{5}','{6}','{7}','{8}')",
                model.ServiceName, model.ServiceID, model.Needapprove, model.CP, model.Isevent, model.Applvl1, model.Applvl2, model.Applvl3, model.PartialName);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool searchByServiceName(ClassifyConfig model)
        {
            var mlist = getClassifyLvl2();
            string n = null;
            bool reslut = true;

            if (mlist.Count > 0)
            {
                foreach (var item in mlist)
                {
                    n = item.ServiceName.ToLower();
                    if (n == model.ServiceName.ToLower() && item.ServiceID == model.ServiceID)
                    {
                        reslut = false;
                        break;
                    }
                }
            }

            return reslut;
        }

        public ClassifyConfig createClassify(OracleDataReader dr)
        {
            ClassifyConfig classify = new ClassifyConfig();
            if (dr["SERVICEID"] is DBNull)
            {
                classify.ServiceName = dr["SERVICENAME"].ToString();
                classify.PartialName = dr["PARTIALNAME"].ToString();
            }
            else
            {
                classify.ID = Convert.ToInt32(dr["ID"]);
                classify.ServiceID = Convert.ToInt32(dr["SERVICEID"]);
                classify.ServiceName = dr["SERVICENAME"].ToString();
                classify.Applvl1 = dr["APPLVL1"].ToString();
                classify.Applvl2 = dr["APPLVL2"].ToString();
                classify.Applvl3 = dr["APPLVL3"].ToString();
                classify.PartialName = dr["PARTIALNAME"].ToString();
                classify.Needapprove = Convert.ToInt32(dr["NEEDAPPROVE"]);
                classify.CP = Convert.ToInt32(dr["CP"]);
                classify.Isevent = Convert.ToInt32(dr["ISEVENT"]);
            }
            return classify;
        }

        public bool deleteClassify(int id)
        {
            string sql = string.Format("delete from ServiceList where id={0}", id);
            int count = oda.ExecuteCommand(sql);
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        public RolesConfig getSingleRoleInfoBy(string accountID)
        {
            string sql = "select roleid,isadmin from userrolerelationship where accountID='" + accountID + "'";
            RolesConfig RC = new RolesConfig();
            using (OracleDataReader dr = oda.ExecuteReader(sql))
            {
                if (dr.Read())
                {
                    RC.accountID = accountID;
                    RC.isAdmin = dr["isadmin"].ToString() == "1" ? "是" : "否";
                    RC.roleName = Enum.GetName(typeof(Wicresoft.SelfService.DAO.RoleCollection), int.Parse(dr["roleID"].ToString()));
                }

            }
            return RC;
        }
        public int updateRoleInfoBy(string accountID, bool isAdmin, int roleID)
        {
            string sql = "update userrolerelationship set isadmin=" + (isAdmin == true ? 1 : 0) + ", roleid=" + roleID + " where accountID='" + accountID + "'";
            return oda.ExecuteCommand(sql);
        }
        public int insertRoleInfoBy(string accountID, bool isAdmin, int roleID)
        {
            string sql = "insert into userrolerelationship (accountID,isAdmin,roleID) values(" + "'" + accountID + "'" + "," + (isAdmin == true ? 1 : 0) + "," + roleID + ")";
            return oda.ExecuteCommand(sql);
        }
        #endregion

        #region common methods
        ///// <summary>  
        /////  生成最终的sql语句
        ///// </summary>  
        ///// <param name="type">标明是select,update,insert还是delete</param>
        ///// <param name="dtName">操作的表名</param>
        ///// <param name="param">传进来的参数，列名+值+列的数据类型， 另外还包含了操作动作，是update的set，还是where</param>
        ///// <param name="orderBy">需要排序的信息，包含列+排序类型</param>
        ///// <param name="ps">ps和pi是用来分页的，为空则不分页，ps为pageSize缩写</param>
        ///// <param name="pi">ps和pi是用来分页的，为空则不分页，pi为pageIndex缩写</param>
        ///// <returns>sql语句</returns>  
        public string PrepareSqlStr(DBMType type, string dtName, QueryParameters[] param, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            string sqlStr = string.Empty;
            string setSqlStr = string.Empty;
            string whereSqlStr = string.Empty;
            string orderbySqlStr = string.Empty;
            PredicateType setType = PredicateType.set;
            PredicateType whereType = PredicateType.where;
            if (param != null)
            {
                setSqlStr = this.PrepareSqlWhereOrSetStr(param, setType, ",");
                whereSqlStr = this.PrepareSqlWhereOrSetStr(param, whereType, " and ");
            }
            if (orderBy != null)
            {
                orderbySqlStr = this.PrepareSqlOrderByStr(orderBy);
            }
            if (type == DBMType.select && ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = pi.Value * ps.Value;
                int endIndex = (pi.Value + 1) * ps.Value;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    orderbySqlStr = "order by null";
                }
                sqlStr = string.Concat("select * from (select   ROW_NUMBER() OVER (", orderbySqlStr, ") RowNum,* from ", dtName, " where 1=1", whereSqlStr, ") a where RowNum between ", beginIndex, " and ", endIndex);
            }
            else
            {
                switch (type)
                {
                    case DBMType.select:
                        sqlStr = string.Concat("select * from ", dtName, whereSqlStr, orderbySqlStr);
                        break;
                    case DBMType.update:
                        sqlStr = string.Concat("update ", dtName, setSqlStr, whereSqlStr);
                        break;
                    case DBMType.insert:
                        string colStr = string.Empty;
                        string valStr = string.Empty;
                        this.getInsertStr(param, out colStr, out valStr);
                        sqlStr = string.Concat("insert into ", dtName, " (", colStr, ") values (", valStr, ")");
                        break;
                    case DBMType.delete:
                        sqlStr = string.Concat("delete from ", dtName, whereSqlStr);
                        break;
                    default:
                        throw new NotImplementedException(string.Concat("Database maintenance type ", Enum.GetName(typeof(DBMType), type), " is not implemented"));
                }
            }

            return sqlStr;
        }
        ///// <summary>  
        /////  生成sql语句orderby部分
        ///// </summary>
        ///// <param name="orderBy">需要排序的信息，包含列+排序类型</param>
        ///// <returns>sql语句orderby部分</returns>  
        public string PrepareSqlOrderByStr(OrderbyParameters[] orderBy)
        {
            string orderByStr = string.Empty;
            if (orderBy != null)
            {
                int length = orderBy.Length;
                bool mark = true;
                for (int i = 0; i < length; i++)
                {
                    OrderbyParameters param = orderBy[i];
                    if (!string.IsNullOrEmpty(param.orderByCol))
                    {
                        if (mark)
                        {
                            orderByStr = " order by ";
                            mark = false;
                        }
                        string order = this.getOrderby(param.orderByType);
                        orderByStr = string.Concat(orderByStr, param.orderByCol, " ", order, ",");
                    }
                }
            }
            return orderByStr == string.Empty ? string.Empty : orderByStr.Substring(0, orderByStr.Length - 1);
        }
        ///// <summary>  
        /////  生成sql语句where或者set（update时候用到）部分
        ///// </summary>
        ///// <param name="queryParameters">传入的参数数组，包含列名+值+列的数据类型</param>
        ///// <param name="type">sql语句谓词，set或者where</param>
        ///// <param name="linkword">子句间隔符号，例如update set colA=valA，colB=valB...这里的linkword就是“，”</param>
        ///// <returns>sql语句where或者set（update时候用到）部分</returns>  
        public string PrepareSqlWhereOrSetStr(QueryParameters[] queryParameters, PredicateType type, string linkword)
        {
            string str = string.Empty;
            if (queryParameters != null)
            {
                int length = queryParameters.Length;
                bool mark = true;
                for (int i = 0; i < length; i++)
                {
                    QueryParameters queryparameter = queryParameters[i];
                    if (!string.IsNullOrEmpty(queryparameter.OracleDbName) && queryparameter.PredicateType == type)
                    {
                        if (mark)
                        {
                            str = string.Concat(" ", Enum.GetName(typeof(PredicateType), type), " ");
                            mark = false;
                        }
                        string columnName = queryparameter.OracleDbName;
                        if (columnName.IndexOf('.') > 0) columnName = columnName.Substring(columnName.LastIndexOf('.') + 1);
                        string opr = this.getOperator(queryparameter.QueryOperator);
                        if (queryparameter.QueryOperator == QueryOperatorType.isNotNull || queryparameter.QueryOperator == QueryOperatorType.isNull)
                        {
                            str = string.Concat(str, "lower(", queryparameter.OracleDbName, ")", opr, linkword);
                        }
                        else
                        {
                            if (queryparameter.PredicateType == PredicateType.set)
                            {
                                str = string.Concat(str, queryparameter.OracleDbName, opr, ":", columnName, Enum.GetName(typeof(PredicateType), type), linkword);
                            }
                            else
                            {
                                str = string.Concat(str, "lower(", queryparameter.OracleDbName, ")", opr, ":", columnName, Enum.GetName(typeof(PredicateType), type), linkword);
                            }
                        }
                    }
                }
            }
            return str == string.Empty ? string.Empty : str.Substring(0, str.Length - linkword.Length);
        }
        public string getOperator(QueryOperatorType type)
        {
            string str = string.Empty;
            switch (type)
            {
                case QueryOperatorType.equal:
                    str = "=";
                    break;
                case QueryOperatorType.notEqual:
                    str = "<>";
                    break;
                case QueryOperatorType.like:
                    str = " like ";
                    break;
                case QueryOperatorType.bigger:
                    str = ">";
                    break;
                case QueryOperatorType.notBigger:
                    str = "<=";
                    break;
                case QueryOperatorType.smaller:
                    str = "<";
                    break;
                case QueryOperatorType.notSmaller:
                    str = ">=";
                    break;
                case QueryOperatorType.isNull:
                    str = " is null ";
                    break;
                case QueryOperatorType.isNotNull:
                    str = " is not null ";
                    break;
                default:
                    throw new InvalidDataException("Undefined operator");
            }

            return str;
        }
        public string getOrderby(orderByType type)
        {
            string str = string.Empty;
            switch (type)
            {
                case orderByType.ascending:
                default:
                    str = "asc";
                    break;
                case orderByType.descending:
                    str = "desc";
                    break;
            }

            return str;
        }
        public string mapRoleNameCN(string roleEN)
        {
            try
            {
                return ConfigurationManager.AppSettings[roleEN];
            }
            catch
            {
                return roleEN;
            }

        }
        public void getInsertStr(QueryParameters[] param, out string colNameStr, out string valueStr)
        {
            colNameStr = string.Empty;
            valueStr = string.Empty;
            foreach (QueryParameters p in param)
            {
                if (string.IsNullOrEmpty(colNameStr) != false)
                {
                    colNameStr = p.OracleDbName;
                }
                else
                {
                    colNameStr = string.Concat(colNameStr, ",", p.OracleDbName);
                }
                if (string.IsNullOrEmpty(valueStr) != false)
                {
                    valueStr = string.Concat(":", p.OracleDbName);
                }
                else
                {
                    valueStr = string.Concat(valueStr, ",:", p.OracleDbName);
                }
            }
        }
        public OracleParameter[] PrepareOracleParameter(QueryParameters[] queryParameters)
        {
            if (queryParameters != null)
            {
                int length = queryParameters.Length;
                OracleParameter[] commandParameters = new OracleParameter[length];
                for (int i = 0; i < length; i++)
                {
                    QueryParameters queryparameter = queryParameters[i];
                    if (!string.IsNullOrEmpty(queryparameter.OracleDbName) && queryparameter.QueryOperator != QueryOperatorType.isNotNull && queryparameter.QueryOperator != QueryOperatorType.isNull)
                    {
                        string columnName = queryparameter.OracleDbName;
                        if (columnName.IndexOf('.') > 0) columnName = columnName.Substring(columnName.LastIndexOf('.') + 1);
                        OracleParameter oracleParameter = new OracleParameter(string.Concat(":", columnName, Enum.GetName(typeof(PredicateType), queryparameter.PredicateType)), queryparameter.OracleDbType, ParameterDirection.Input);
                        oracleParameter.Value = queryparameter.OracleDbValue;
                        commandParameters[i] = oracleParameter;
                    }
                }
                return commandParameters;
            }
            else
            {
                return null;
            }
        }
        public void commitTransaction()
        {
            if (oTran != null) oTran.Commit();
        }
        public void rollbackTransaction()
        {
            if (oTran != null) oTran.Rollback();
        }
        public void closeDBConnection()
        {
            if (oda != null && oda.conn != null) oda.Close();
        }
        public void Dispose()
        {
            closeDBConnection();
        }
        #endregion

        #region webService methods
        public int UpdateServiceRecords(string requestID, string status, string approver, string incidentNumber)
        {
            int count = 0;
            string sqlStr = string.Empty;
            string setStr = string.Empty;
            List<OracleParameter> parmList = new List<OracleParameter>();
            bool update = false;
            if (!string.IsNullOrEmpty(status))
            {
                update = true;
                setStr = setStr + "stauts=:status,";
                parmList.Add(new OracleParameter("status", OracleDbType.Int32, mapStatus(status), ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(approver))
            {
                update = true;
                setStr = setStr + "approveowner=:approver,";
                parmList.Add(new OracleParameter("approver", OracleDbType.NVarchar2, approver, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(incidentNumber))
            {
                update = true;
                setStr = setStr + "incidentNumber=:incidentNumber,";
                parmList.Add(new OracleParameter("incidentNumber", OracleDbType.NVarchar2, incidentNumber, ParameterDirection.Input));
            }
            if (update)
            {
                setStr = setStr.Trim(',');
                sqlStr = "update servicerecords set " + setStr + "  where serviceRequestID=:serviceRequestID";
                parmList.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, requestID, ParameterDirection.Input));
                OracleParameter[] commandParameters = parmList.ToArray();
                if (oTran == null)
                {
                    oda.Open();
                    oTran = oda.conn.BeginTransaction();
                }
                count = oda.ExecuteCommand(sqlStr, commandParameters);
            }
            return count;
        }

        private int mapStatus(string status)
        {
            switch (status)
            {
                case "Assigned":
                    return 2;
                case "In Progress":
                    return 3;
                case "Pending":
                    return 3;
                case "Resolved":
                    return 3;
                case "Closed":
                    return 4;
                default:
                    throw new ArgumentException("状态参数\"" + status + "\"错误，无法匹配");
            }
        }
        #endregion


    }
}
